<?php
class Service {
	public function Service(){}
	
	public function getUserByLogin($lg){
		$user = null;
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(false);
			
			$sql = 'select user_id, full_name, 
				login, pass, pass_ro, email, send_email_ind, 
				admin_ind, connect_ind 
				from user 
				where login = ?';
			
			$stat = $this->prepare($conn, $sql);
			
			$stat->bind_param('s', $lg); 
			$stat->bind_result($id, $name, $login, $pass, $passRo, $email, $sendEmail, $admin, $connect);
			
			$this->execute($stat);
			
			if($stat->fetch()){
				$user = new User();
				
				$user->setId($id); 
				$user->setName($name); 
				$user->setLogin($login); 
				$user->setPass($pass); 
				$user->setPassRo($passRo); // password for readonly access
				$user->setEmail($email); 
				$user->setSendEmail($this->getBoolean($sendEmail)); 
				$user->setAdmin($this->getBoolean($admin)); 
				$user->setConnect($this->getBoolean($connect));
			}
			
			$this->closeStatement($stat);
		}
		catch(Exception $e){
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
		
		return $user;
	}
	
	public function getUsers(){
		$res = array();
		
		$conn = null;
		$stat = null;
		$error = null;

		try {
			$conn = $this->getConnection(false);
			$sql = 'select user_id, full_name, 
				login, pass, pass_ro, email, send_email_ind, 
				admin_ind, connect_ind 
				from user order by login';
			
			$stat = $this->prepare($conn, $sql);
			$stat->bind_result($id, $name, $login, $pass, $passRo, $email, 
					$sendEmail, $admin, $connect);
			
			$this->execute($stat);
			
			while($stat->fetch()){
				$user = new User();
				
				$user->setId($id); 
				$user->setName($name); 
				$user->setLogin($login); 
				$user->setPass($pass); 
				$user->setPassRo($passRo); // password for readonly access
				$user->setEmail($email); 
				$user->setSendEmail($this->getBoolean($sendEmail)); 
				$user->setAdmin($this->getBoolean($admin)); 
				$user->setConnect($this->getBoolean($connect));
				
				$res[] = $user;
			}
		}
		catch(Exception $e){
			$error = $e;
		}
		
		$this->closeStatement($stat);
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
		
		return $res; 
	}
	
	public function deletePictures($ids){
		$conn = null;
		$error = null; 
		
		try {
			$conn = $this->getConnection(true);
			
			$sql = 'delete from picture where picture_id in (' . $this->getListString($ids) . ')';
			$stat = $this->prepare($conn, $sql);
			
			$this->execute($stat);
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function updateProfile($id, $login, $name, $email, $sendEmail){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			
			$sql = 'update user set login = ?, full_name = ?, email = ?, send_email_ind = ? where user_id = ?';
			$stat = $this->prepare($conn, $sql);
			
			$sendm = $this->setBoolean($sendEmail);
			$stat->bind_param('sssss', $login, $name, $email, $sendm, $id);
			
			$this->execute($stat);
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function setBoolean($bool){
		return $bool ? 'Y' : 'N';
	}
	
	public function getUserById($id){
		$user = null;
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(false);
			
			$sql = 'select user_id, full_name, 
				login, pass, pass_ro, email, send_email_ind, 
				admin_ind, connect_ind 
				from user 
				where user_id = ?';
			
			$stat = $this->prepare($conn, $sql);
			
			$stat->bind_param('s', $id); 
			$stat->bind_result($id, $name, $login, $pass, $passRo, $email, $sendEmail, $admin, $connect);
			
			$this->execute($stat);
			
			if($stat->fetch()){
				$user = new User();
				
				$user->setId($id); 
				$user->setName($name); 
				$user->setLogin($login); 
				$user->setPass($pass); 
				$user->setPassRo($passRo); // password for readonly access
				$user->setEmail($email); 
				$user->setSendEmail($this->getBoolean($sendEmail)); 
				$user->setAdmin($this->getBoolean($admin)); 
				$user->setConnect($this->getBoolean($connect));
			}
			
			$this->closeStatement($stat);
		}
		catch(Exception $e){
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
		
		return $user;
	}
	
	public function addAlbum($id, $name, $userId, $seqNo){
		$conn = null;
		$error = null;
		
		try {
			// check the albume is filled out
			if(empty($name)){
				throw new Exception('Album name is required, please fill out the name');
			}
			
			$conn = $this->getConnection(true);
			$sql = 'insert into album 
					(album_id, user_id, album_name, seq_no, picture_id) 
					values 
					(?, ?, ?, ?, ?)';
			
			$stat = $this->prepare($conn, $sql);
			$pictureId = null;
			$stat->bind_param('sssds', $id, $userId, $name, $seqNo, $pictureId);
			
			$this->execute($stat);
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}

	public function updateAlbum($id, $name){
		$conn = null;
		$error = null;
		
		try {
			// check the albume is filled out
			if(empty($name)){
				throw new Exception('Album name is required, please fill out the name');
			}
			
			$conn = $this->getConnection(true);
			
			$sql = 'update album set album_name = ? where album_id = ?';
			$stat = $this->prepare($conn, $sql);
			
			$stat->bind_param('ss', $name, $id);
			$this->execute($stat);
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function getMaximumAlbumSeq() {
		$res = 0;
		$conn = null;
		$error = null;
		
		try {
			
			$conn = $this->getConnection(false);
			$sql = 'select max(seq_no) from album';
			
			$stat = $this->prepare($conn, $sql);
			$stat->bind_result($val);
			
			$stat->execute();
			$stat->fetch();
			
			$res = $val;
		}
		catch(Exception $e){
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
		
		return $res;
	}
	
	public function getMaxPictureSeq(){
		$res = 0;
		$conn = null;
		$error = null;
		
		try {
			
			$conn = $this->getConnection(false);
			$sql = 'select max(seq_no) from picture';
			
			$stat = $this->prepare($conn, $sql);
			$stat->bind_result($val);
			
			$stat->execute();
			$stat->fetch();
			
			$res = $val;
		}
		catch(Exception $e){
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
		
		return $res;
	}
	
	public function changeReadonlyPassword($id, $change){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			$sql = 'update user set pass_ro = ? where user_id = ?';
			
			$stat = $this->prepare($conn, $sql);
			$stat->bind_param('ss', $change, $id);
			
			$this->execute($stat);
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($erro != null){
			throw $error;
		}
	}
	
	public function changeEditPassword($id, $change) {
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			$sql = 'update user set pass = ? where user_id = ?';
			
			$stat = $this->prepare($conn, $sql);
			$stat->bind_param('ss', $change, $id);
			
			$this->execute($stat);
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($erro != null){
			throw $error;
		}
	}
	
	public function getNewId(){
		return uniqid(rand(), true);
	}
	
	public function deleteUsers($selected){
		$conn = null;
		$error = null; 
		
		try {
			$conn = $this->getConnection(true);
			
			$sql = 'delete from user where user_id in (' . $this->getListString($selected) . ')';
			$stat = $this->prepare($conn, $sql);
			
			$this->execute($stat);
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function searchAlbums($userId, $search){
		$conn = null;
		$error = null;
		$res = array();
		
		try {
			$conn = $this->getConnection(false);
			
			$sql = 'select album_id, user_id, album_name, 
					seq_no, picture_id from album where user_id = ? and album_name like ?   
					order by seq_no, album_name ';
			
			$stat = $this->prepare($conn, $sql);
			
			$sr = "%{$search}%";
			if($search == null || trim($search) == ''){
				$sr = '%';
			}
			
			$stat->bind_param('ss', $userId, $sr);
			
			if($stat->error){
				throw new Exception($stat->error);
			}
			
			$stat->bind_result($id, $uid, $name, $seq, $pictId); 
			
			$this->execute($stat);
			
			while($stat->fetch()){
				$album = new Album();
				
				$album->setId($id);
				$album->setName($name);
				$album->setPictureId($pictId);
				$album->setSeqNo($seq);
				$album->setUserId($uid);
				
				$res[] = $album; 
			}
		}
		catch(Exception $e){
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error; 
		}
		
		return $res;
	} 
	
	public function addUser($user){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			$sql = 'insert into user 
				(user_id, full_name, login, pass, pass_ro, 
				email, send_email_ind, admin_ind, connect_ind) 
				values (?, ?, ?, ?, ?, ?, ?, ?, ?)';
			
			$stat = $this->prepare($conn, $sql); 
			
			$id = $user->getId(); 
			$name = $user->getName(); 
			$login = $user->getLogin(); 
			$pass = $user->getPass(); 
			$passRo = $user->getPassRo(); 
			$email = $user->getEmail(); 
			$sendEmail = $this->setBoolean($user->isSendEmail()); 
			$admin = $this->setBoolean($user->isAdmin()); 
			$connect = $this->setBoolean($user->isConnect());
			
			$stat->bind_param('sssssssss', $id, $name, 
					$login, $pass, $passRo, $email, $sendEmail, $admin, $connect);
					
			$this->execute($stat);
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function updateUser($user){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			$sql = 'update user set full_name = ?, login = ?, 
					email = ?, send_email_ind = ?, admin_ind = ?, connect_ind = ? where user_id = ?';
			
			$stat = $this->prepare($conn, $sql);
			
			$id = $user->getId(); 
			$name = $user->getName(); 
			$login = $user->getLogin(); 
			$email = $user->getEmail(); 
			$sendEmail = $this->setBoolean($user->isSendEmail()); 
			$admin = $this->setBoolean($user->isAdmin()); 
			$connect = $this->setBoolean($user->isConnect());
			
			$stat->bind_param('sssssss', $name, $login, $email, $sendEmail, $admin, $connect, $id);
			$this->execute($stat); 
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function addPicture($picture){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			
			$sql = 'insert into picture (picture_id, album_id, comments, seq_no) values (?, ?, ?, ?)';
			$stat = $this->prepare($conn, $sql);
			
			
			$id = $picture->getId();
			$albumId = $picture->getAlbumId();
			$comments = $picture->getComments();
			$seqNo = $picture->getSeqNo();
			
			$stat->bind_param('sssd', $id, $albumId, $comments, $seqNo);
			
			$this->execute($stat); 
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function updatePicture($picture){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);

			$sql = 'update picture set comments = ? where picture_id = ?';
			$stat = $this->prepare($conn, $sql);
			
			$id = $picture->getId();
			$comments = $picture->getComments();
			
			$stat->bind_param('ss', $comments, $id);
			
			$this->execute($stat); 
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function deleteAlbums($ids){
		$conn = null;
		$error = null; 
		
		try {
			$conn = $this->getConnection(true);
			
			$sql = 'delete from album where album_id in (' . $this->getListString($ids) . ')';
			$stat = $this->prepare($conn, $sql);
			
			$this->execute($stat);
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function commit($connection){
		$connection->commit();
		
		if($connection->error){
			throw new Exception($connection->error);
		}
	}
	
	public function prepare($connection, $sql){
		$stat = $connection->prepare($sql); 
		
		if($connection->error){
			throw new Exception($connection->error);
		}
		
		return $stat; 
	}
	
	public function execute($statement){
		$statement->execute();
		
		if($statement->error){
			throw new Exception($statement->error);
		}
	}
	
	public function closeConnection($connection){
		if($connection != null){
			try {
				$connection->close();
			}
			catch(Exception $e){
				// nothing
			}
		}
	}
	
	public function closeStatement($statement){
		if($statement != null){
			try {
				$statement->close();
			}
			catch(Exception $e){
				// nothing
			}
		}
	}
	private function getConnection($transaction){
        $conn  = new mysqli(PictureConfig::$MACHINE, PictureConfig::$LOGIN, 
        		PictureConfig::$PASSWORD, PictureConfig::$DATABASE, PictureConfig::$PORT);
        
        if($conn->error){
        	throw new Exception($conn->error);
        }
        
        $conn->autocommit(! $transaction);
        
        return $conn;
	}
	
	private function rollback($c){
		try {
			if($c != null){
				$c->rollback();
			}
		}
		catch(Exception $e){
			// no implementation needed
		}
	}
	
	public function getBoolean($val){
		return $val == 'y' || $val == 'Y';
	}
	
	public function getListString($ids){
		$res = '';
		
		if(is_array($ids) && count($ids) > 0){
			$res = '\'' . join('\', \'', $ids) . '\''; 
		}
		
		return $res;
	}
	
	public function getPicturesByAlbumId($id){
		$conn = null;
		$error = null;
		$arr = array();
		
		try {
			$conn = $this->getConnection(false);
			
			$sql = 'select picture_id, album_id, comments, seq_no from picture where album_id = ? order by seq_no';
			
			$stat = $this->prepare($conn, $sql);
			
			$stat->bind_param('s', $id);
			$stat->bind_result($pid, $aid, $comments, $seq);
			
			$this->execute($stat);
			
			while($stat->fetch()){
				$picture = new Picture();
				
				$picture->setAlbumId($aid);
				$picture->setId($pid);
				$picture->setComments($comments);
				$picture->setSeqNo($seq);
				
				$arr[] = $picture; 
			}
		}
		catch(Exception $e){
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
		
		return $arr;
	}
	
	public function setAlbumDefaultId($albumId, $id){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			$sql = "update album set picture_id = ? where album_id = ?";
			$stat = $this->prepare($conn, $sql);
			$stat->bind_param('ss', $id, $albumId);
			
			$this->execute($stat);
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function renumberAlbums($id){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);

			// get the ids for the specified user ordered by the sequence number and for the specified user
			// then start renumbering and operating changes to the database the same time
			$items = $this->getAllAlbums($conn, $id);
			$this->redoAlbumsSeq($conn, $items);
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
		
	}
	
	private function getAllAlbums($conn, $id){
		$arr = array();
		
		$sql = 'select album_id from album where user_id = ? order by seq_no, album_name ';
		$stat = $this->prepare($conn, $sql);
		$stat->bind_param('s', $id);
		$stat->bind_result($albumId);
		
		$this->execute($stat);
		
		// now read the ids associated
		while($stat->fetch()){
			$arr[] = $albumId;
		}
		
		$stat->close();
		
		return $arr;
	}
	
	private function redoAlbumsSeq($conn, $ids){
		$index = 10;
		$sql = 'update album set seq_no = ? where album_id = ?';
		$stat = $this->prepare($conn, $sql);
		
		foreach($ids as $current){
			$stat->bind_param('ds', $index, $current);
			
			$this->execute($stat);
			$index = $index + 10;
		}
	}
	
	public function renumberPictures($id){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);

			// get the ids for the specified user ordered by the sequence number and for the specified user
			// then start renumbering and operating changes to the database the same time
			$items = $this->getAllPictures($conn, $id);
			$this->redoPicturesSeq($conn, $items);
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
		
	}
	
	private function getAllPictures($conn, $id){
		$sql = 'select p.picture_id from picture p, album a
				where p.album_id = a.album_id
				and a.user_id = ? order by p.seq_no, p.comments';
		
		$stat = $this->prepare($conn, $sql);
		$stat->bind_param('s', $id);
		$stat->bind_result($pictureId);
		$this->execute($stat);
		
		$arr = array();
		
		while($stat->fetch()){
			$arr[] = $pictureId;
		}
		
		return $arr;
	}
	
	private function redoPicturesSeq($conn, $ids){
		$sql = 'update picture set seq_no = ? where picture_id = ?';
		$stat = $this->prepare($conn, $sql);
		
		$val = 10;
		foreach($ids as $id){
			$stat->bind_param('ds', $val, $id);
			
			$stat->execute();
			$val = $val + 10;
		}
	}
	
	public function switchSequenceAlbums($id, $other){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			
			$first = $this->getAlbumSeq($conn, $id);
			$second = $this->getAlbumSeq($conn, $other);
			
			$this->updateAlbumSeq($conn, $id, $second);
			$this->updateAlbumSeq($conn, $other, $first);
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	private function getAlbumSeq($conn, $id){
		$sql = "select seq_no from album where album_id = ?";
		
		$stat = $this->prepare($conn, $sql);
		$stat->bind_param('s', $id);
		$stat->bind_result($seq);
		
		$this->execute($stat);
		
		$res = 0;
		if($stat->fetch()){
			$res = $seq;
		}
		
		return $res;
	}
	
	private function updateAlbumSeq($conn, $id, $value){
		$sql = 'update album set seq_no = ? where album_id = ?';
		$stat = $this->prepare($conn, $sql); 
		
		$stat->bind_param('ds', $value, $id);
		$this->execute($stat);
	}
	
	public function setAlbumsId($ids, $moveId){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			
			// this time we try with one by one processing
			$sql = 'update album set user_id = ? where album_id = ?'; 
			$stat = $this->prepare($conn, $sql);
			
			foreach($ids as $id){
				$stat->bind_param('ss', $moveId, $id);
				
				$this->execute($stat);
			}
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function getJobs($userId){
		$conn = null;
		$error = null;
		$arr = array();
		
		try {
			$conn = $this->getConnection(false);
			
			// this time we try with one by one processing
			$sql = 'select job_id, user_id, job_name, status_cd from job where user_id = ? order by job_name'; 
			$stat = $this->prepare($conn, $sql);
			
			$stat->bind_param('s', $userId);
			$stat->bind_result($id, $uid, $name, $status);
			
			$this->execute($stat);
			while($stat->fetch()){
				$job = new Job();
				
				$job->setId($id);
				$job->setName($name);
				$job->setStatus($status);
				$job->setUserId($uid);
				
				$arr[] = $job;
			}
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
		
		return $arr;
	}
	
	public function getJobStatusList(){
		return array(
			PictureConfig::$JOB_STATUS_BRAND_NEW, 
			PictureConfig::$JOB_STATUS_READY_PROCESS, 
			PictureConfig::$JOB_STATUS_PENDING, 
			PictureConfig::$JOB_STATUS_PROCESSED);
	}
	
	public function deleteJobs($ids){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			
			$this->checkCanDeleteJob($conn, $ids);
			$this->deleteJobPictures($conn, $ids);
			$this->deleteParentJobs($conn, $ids);
			$this->deleteJobFiles($ids);
			
			
			$this->commit($conn);
		}
		catch(Exception $err){
			$this->rollback($conn);
			$error = $err; 
		}
		
		$this->closeConnection($conn);
		
		if($error){
			throw $error;
		}
	}
	
	private function deleteJobFiles($ids){
		$content = PictureConfig::$CONTENT_FOLDER; 
		
		foreach($ids as $current){
			$filename = "{$content}jobs/job_{$current}.dat";
			
			if(is_file($filename)){
				unlink($filename);
			}
		}
	}
	
	private function checkCanDeleteJob($conn, $ids){
		$idlist = $this->getListString($ids);
		$statuslist = $this->getListString(array(PictureConfig::$JOB_STATUS_PENDING, PictureConfig::$JOB_STATUS_READY_PROCESS));
		
		$sql = "select job_id from job j where job_id in ({$idlist}) and status_cd in ({$statuslist})";
		$stat = $this->prepare($conn, $sql);
		$this->execute($stat);
		
		$res = $stat->fetch();
		$this->closeStatement($stat);
		
		if($res){
			throw new Exception('At least one job is in pending or ready to process state, you cannot delete it');
		}
	}
	
	private function deleteJobPictures($conn, $ids){
		$list = $this->getListString($ids);
		$sql = "delete from job_picture where job_id in ({$list})";
		
		$stat = $this->prepare($conn, $sql);
		$stat->execute();
		$this->closeStatement($stat);
	}
	
	private function deleteParentJobs($conn, $ids){
		$list = $this->getListString($ids);
		$sql = "delete from job where job_id in ({$list})";
		
		$stat = $this->prepare($conn, $sql);
		$stat->execute();
		$this->closeStatement($stat);
	}
			
	public function addJob($job){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			
			// this time we try with one by one processing
			$sql = 'insert into job (job_id, user_id, job_name, status_cd) values (?, ?, ?, ?)'; 
			$stat = $this->prepare($conn, $sql);
			
			$id = $job->getId();
			$userId = $job->getUserId();
			$name = $job->getName();
			$status = $job->getStatus();
			
			$stat->bind_param('ssss', $id, $userId, $name, $status);
			$this->execute($stat);
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function updateJob($job){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			
			// this time we try with one by one processing
			$sql = 'update job set user_id = ?, job_name = ?, status_cd = ? where job_id = ?';
			$stat = $this->prepare($conn, $sql);
			
			$id = $job->getId();
			$userId = $job->getUserId();
			$name = $job->getName();
			$status = $job->getStatus();
			
			$stat->bind_param('ssss', $userId, $name, $status, $id);
			$this->execute($stat);
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function removeJobPictures($jobId, $ids){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			
			// this time we try with one by one processing
			$list = $this->getListString($ids);
			
			$sql = "delete from job_picture where job_id = '{$jobId}' and picture_id in ({$list})";
			
			$stat = $this->prepare($conn, $sql);
			$this->execute($stat);
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function addJobPictures($jobId, $ids){
		$conn = null;
		$error = null;
		
		try {
			$conn = $this->getConnection(true);
			$sql = 'insert into job_picture (job_picture_id, job_id, picture_id) values (?, ?, ?)';
			
			$stat = $this->prepare($conn, $sql);
			
			foreach($ids as $id){
				$newid = $this->getNewId(); 
				
				$stat->bind_param('sss', $newid, $jobId, $id);
				
				$this->execute($stat);
			}
			
			$this->commit($conn);
		}
		catch(Exception $e){
			$this->rollback($conn);
			
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
	}
	
	public function getJobPictures($jobId){
		$conn = null;
		$error = null;
		$arr = array();
		
		try {
			$conn = $this->getConnection(false);
			
			$sql = 'select picture_id from job_picture where job_id = ?';
			$stat = $this->prepare($conn, $sql);
			
			$stat->bind_param('s', $jobId);
			$stat->bind_result($pictureId);
			
			$this->execute($stat);
			while($stat->fetch()){
				$arr[] = $pictureId;
			}
			
			$this->closeStatement($stat);
		}
		catch(Exception $e){
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
		
		return $arr;
	}
	
	public function getAlreadyJobPictureIds($jobId, $ids){
		$conn = null;
		$error = null;
		$arr = array();
		
		try {
			$conn = $this->getConnection(false);
			
			$sql = 'select picture_id from job_picture where job_id = ? and picture_id in (' . $this->getListString($ids) . ')';
			$stat = $this->prepare($conn, $sql);
			
			$stat->bind_param('s', $jobId);
			$stat->bind_result($pictureId);
			
			$this->execute($stat);
			while($stat->fetch()){
				$arr[] = $pictureId;
			}
			
			$this->closeStatement($stat);
		}
		catch(Exception $e){
			$error = $e;
		}
		
		$this->closeConnection($conn);
		
		if($error != null){
			throw $error;
		}
		
		return $arr;
	}
	
	public function getJob($id){
		$conn = null;
		$error = null;
		$res = null;
		
		try {
			$conn = $this->getConnection(false); 
			$sql = "select job_id, user_id, job_name, status_cd from job where job_id = ?";
			$stat = $this->prepare($conn, $sql);
			
			$stat->bind_param('s', $id);
			$stat->bind_result($jobId, $user, $name, $status);
			
			$this->execute($stat);
			if($stat->fetch()){
				$res = new Job();

				$res->setId($jobId);
				$res->setName($name);
				$res->setStatus($status);
				$res->setUserId($user);
			}
			
		}
		catch(Exception $err){
			$error = $err; 
		}
		
		$this->closeConnection($conn);
		
		if($error){
			throw $error; 
		}
		
		return $res;
	}
	
} 
?>